﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{
   public class userDAL
    {
        /// <summary>
        /// 用户
        /// </summary>
        /// <param name="us"></param>
        /// <returns></returns>
        //用户或管理员添加
        public static int addUser(User us)
        {
            string sql = "insert into tb_User(UserName,Pwd,UserTypeID,Sex,Age,Phone) values(@UserName,@Pwd,@UserTypeID,@Sex,@Age,@Phone)";
            SqlParameter[] para = {
            new SqlParameter("@UserName",us.UserName),
            new SqlParameter("@Pwd",us.Pwd),
            new SqlParameter("@UserTypeID",us.UserTypeID),
            new SqlParameter("@Sex",us.Sex),
            new SqlParameter("@Age",us.Age),
            new SqlParameter("@Phone",us.Phone),
            };
            return SqlDBHelper.ExecuteNonQuery(sql, para);
        }
        //用户添加
        public static int addUserYH(User us)
        {
            string sql = "insert into tb_User(UserName,Pwd,Phone) values(@UserName,@Pwd,@Phone)";
            SqlParameter[] para = {
            new SqlParameter("@UserName",us.UserName),
            new SqlParameter("@Pwd",us.Pwd),
            new SqlParameter("@Phone",us.Phone),
            };
            return SqlDBHelper.ExecuteNonQuery(sql, para);
        }
        //用户或管理员添加判断重复
        public static object UserCF(string name) {
            string sql = "select * from tb_User where UserName='" + name + "'";
            return SqlDBHelper.ExecuteScalar(sql);
        }
        //管理员查询
        public static DataTable selectUser() {
            string sql = "select u.UserID,u.UserName,u.Pwd,ut.Name,u.Sex,u.Age,u.Phone,u.Titles from tb_User u,tb_UserTypeID ut where u.UserTypeID=ut.UserTypeID";
            return SqlDBHelper.GetDataTable(sql);
        }
        //分类查询
        public static DataTable flCX(string sq) {
            string sql = "select * from tb_User u,tb_UserTypeID ut where u.UserTypeID=ut.UserTypeID "+sq;
            return SqlDBHelper.GetDataTable(sql);
        }
        //管理员封号
        public static int updateFH(int id) {
            string sql = "update tb_User set Titles=1 where UserID="+id;
            return SqlDBHelper.ExecuteNonQuery(sql);
        }
        //管理员取消封号
        public static int updateJH(int id)
        {
            string sql = "update tb_User set Titles=0 where UserID=" + id;
            return SqlDBHelper.ExecuteNonQuery(sql);
        }
        //管理员删除
        public static int delete(int id)
        {
            string sql = "delete from tb_User where UserID=" + id;
            return SqlDBHelper.ExecuteNonQuery(sql);
        }
        //管理后台登录
        public static DataTable login(string name,string pwd) {
            string sql = "select * from tb_User where UserName='"+name+ "' and Pwd='" + pwd + "' and Titles=0";
            return SqlDBHelper.GetDataTable(sql);
        }
        //前台手机号登录
        public static DataTable phoneDL(string phone)
        {
            string sql = "select * from tb_User where Phone="+phone;
            return SqlDBHelper.GetDataTable(sql);
        }
        //手机号查询用户名
        public static object phoneUser(string phone)
        {
            string sql = "select UserName from tb_User where Phone=" + phone;
            return SqlDBHelper.ExecuteScalar(sql);
        }
        //查看是否被封号
        public static object sfFH(string name, string pwd)
        {
            string sql = "select Titles from tb_User where UserName='" + name + "' and Pwd='" + pwd + "'";
            return SqlDBHelper.ExecuteScalar(sql);
        }
        //修改密码
        public static int updatePwd(string name,string pwd) {
            string sql = "update tb_User set Pwd='"+pwd+"' where UserName='"+name+"'";
            return SqlDBHelper.ExecuteNonQuery(sql);
        }
        //修改自己
        public static int updateUser(User us,string name) {
            string sql = "update tb_User set UserName=@UserName,Sex=@Sex,Age=@Age,Phone=@Phone where UserName='" + name + "'";
            SqlParameter[] para = { 
            new SqlParameter("@UserName",us.UserName),
            new SqlParameter("@Sex",us.Sex),
            new SqlParameter("@Age",us.Age),
            new SqlParameter("@Phone",us.Phone),
            };
            return SqlDBHelper.ExecuteNonQuery(sql,para);
        }
        //有用户名查询全部信息
        public static DataTable selectName(string name) {
            string sql = "select * from tb_User where UserName='" + name + "'";
            return SqlDBHelper.GetDataTable(sql);
        }
        //查询数据条数
        public static object dataNum() {
            string sql = "select count(*) from tb_User";
            return SqlDBHelper.ExecuteScalar(sql);
        }
        //下拉框
        public static List<Models.User> liXlk()
        {
            string sql = "select * from tb_User";
            SqlDataReader dr = SqlDBHelper.GetReader(sql);
            List<Models.User> lis = new List<Models.User>();
            while (dr.Read())
            {
                lis.Add(new Models.User()
                {
                    UserID = Convert.ToInt32(dr["UserID"]),
                    UserName = dr["UserName"].ToString()
                });
            }
            dr.Close();
            return lis;
        }
        //判断是否是管理员
        public static object userGL(string name) {
            string sql = "select UserTypeID from tb_User where UserName='"+ name + "'";
            return SqlDBHelper.ExecuteScalar(sql);
        }
        //判断是否是管理员id
        public static object idGL(int id)
        {
            string sql = "select UserTypeID from tb_User where UserID='" + id + "'";
            return SqlDBHelper.ExecuteScalar(sql);
        }
        //上传头像
        public static int updateImage(User us)
        {
            string sql = "update tb_User set Image=@Image where UserID=@UserID";
            SqlParameter[] para = {
            new SqlParameter("@UserID",us.UserID),
            new SqlParameter("@Image",us.Image)
            };
            return SqlDBHelper.ExecuteNonQuery(sql, para);
        }
        

    }
}
